/**
 * @Description 免修管理dao层
 * @author 20155873 张联富
 * @create 2018-04-12 14:07
 **/
package com.campus_information_portal.dao;

import com.campus_information_portal.entity.Academy;
import com.campus_information_portal.entity.ExemStuAcaTerm;
import com.campus_information_portal.entity.Exemption;
import com.campus_information_portal.entity.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * @author 20155873 张联富
 * @className ExemptionDao
 * @Description 免修管理dao层
 * @create 2018/4/19 9：46
 **/
public interface ExemptionDao {
    /**
     * @param stu_Info_Id
     * @Description 根据学生id查询一个6级成绩
     * @author 20155873 张联富
     * @methodName queryCet6
     * @returnType int
     * @create 2018/4/19 9：46
     **/
    @Select("select cet_6_Grade from cet_46 where stu_Info_Id=#{stu_Info_Id}")
    int queryCet6(@Param("stu_Info_Id") int stu_Info_Id);


    /* *
     * @param  * @param stu_Info_Id
     * @Description 根据id查找是否已申请过记录和是否被批准
     * @methodName selectExemptionByid
     * @author 20155873 张联富
     * @date 2018/4/19 14:25
     * @return Exemption
     */
    @Select("select * from exemption where stu_Info_Id=#{stu_Info_Id}")
    List<Exemption> selectExemptionByid(@Param("stu_Info_Id") int stu_Info_Id);


    /* *
     * @param  * @param stu_Info_Id
     * @Description 学生端：查询自己的所有申请记录
     * @methodName selectExemptionByid
     * @author 20155873 张联富
     * @date 2018/4/19 14:25
     * @return Exemption
     */
    @Select( "select exemption.*  ,student.stu_num,student.stu_name,academy.academy_name " +
            "            from exemption ,student,academy " +
            "            WHERE  student.stu_info_id=exemption.stu_info_id  and academy.academy_id=student.academy_id " +
            "            and exemption.stu_Info_Id=#{stu_Info_Id} ")
    List<ExemStuAcaTerm> selectExemStuAcaTermByid(@Param("stu_Info_Id") int stu_Info_Id);



    /**
     * @param pageNo pageSize
     * @Description 查找全部免修信息（分页）
     * @author 20155873 张联富
     * @methodName queryAllExemption
     * @returnType list
     * @create 2018/4/19 9：46
     * SELECT * FROM table WHERE exhibition_id=1
     **/
    @Select("select * from exemption where exemption_Flag=#{exemption_Flag} LIMIT #{pageNo},#{pageSize} ")
    List<Exemption>  queryAllExemption(@Param("pageNo") int pageNo , @Param("pageSize") int pageSize,@Param("exemption_Flag") int exemption_Flag );


    /**
     * @param exemption_id
     *   @param exemption_Flag
     *   @param  dispose_time
     * @Description 根据免修id修改标志位(批准/退回)
     * @author 20155873 张联富
     * @methodName manageExemption
     * @returnType void
     * @create 2018/4/19 9：46
     **/
    @Select("update exemption set exemption_Flag=#{exemption_Flag},dispose_time=#{dispose_time}  where exemption_id=#{exemption_id}")
   void manageExemption(@Param("exemption_id") int exemption_id,@Param("exemption_Flag") int exemption_Flag,@Param("dispose_time") Date dispose_time);


    /**
     * @param stu_info_id
     * @param apply_time
     * @Description 申请免修
     * @author 20155873 张联富
     * @methodName addExemption
     * @returnType void
     * @create 2018/4/19 9：46
     **/
    @Select("insert into exemption(stu_info_id,apply_time) values(#{stu_info_id},#{apply_time})")
    void addExemption(@Param("stu_info_id") int stu_info_id,@Param("apply_time") Date apply_time);



    /**
     * @param
     * @Description 计算免修记录数
     * @author 20155873 张联富
     * @methodName obtainCount
     * @returnType Long
     * @create 2018/5/3 8：43
     **/
    @Select("select count(e.exemption_id) from exemption as e")
    Long obtainCount();

    /**
     * @param
     * @Description 查询学生部分信息
     * @author 20155873 张联富
     * @methodName queryStudentByStuNum
     * @returnType Student
     * @create 2018/5/3 15:08
     **/
    @Select("select * from student where stu_info_id=#{stu_info_id}")
    Student queryStudentByStuId(@Param("stu_info_id") int stu_info_id);





    /**
     * @param pageNo pageSize
     * @Description 老师端在表格中查看到的学生信息简况
     * @author 20155873 张联富
     * @methodName queryAllEmp
     * @returnType List<ExemStuAcaTerm>
     * @create 2018/5/10 8:48
     **/
    @Select("select exemption.exemption_id , student.stu_num ,student.stu_name,exemption.apply_time,academy.academy_name " +
            "from exemption ,student,academy " +
            "WHERE exemption.stu_info_id=student.stu_info_id " +
            "AND student.academy_id=academy.academy_id and exemption_flag=#{exemption_flag} " +
            "LIMIT   #{pageNo} , #{pageSize} ")
    public List<ExemStuAcaTerm> queryAllEmp(@Param("pageNo") int pageNo , @Param("pageSize") int pageSize,@Param("exemption_flag") int exemption_flag);

    /**
     * @param
     * @Description 老师端查看到的单个学生信息详情
     * @author 20155873 张联富
     * @methodName queryOneExe
     * @returnType ExemStuAcaTerm
     * @create 2018/5/10 14:16
     **/
    @Select("select exemption.*  ,student.stu_num,student.stu_name,academy.academy_name,avg(mark.mark) as mark " +
            "            from exemption ,student,academy ,mark " +
            "            WHERE  student.stu_info_id=exemption.stu_info_id  and academy.academy_id=student.academy_id and mark.stu_info_id= exemption.stu_info_id " +
            "            and exemption.exemption_id=#{exemption_id} and mark.term_id=(select MAX(term_id)-1 from term)" )
    List<ExemStuAcaTerm> queryOneExe(@Param("exemption_id") int exemption_id);

   // "select exemption.* , student.stu_num ,student.stu_name,academy.academy_name,avg(mark.mark) " +
    //            "from exemption ,student,academy ,mark " +
    //            "WHERE student.stu_info_id=exemption.stu_info_id  and academy.academy_id=student.academy_id and mark.stu_info_id= exemption.stu_info_id" +
    //            "and exemption.exemption_id= and  mark.term_id=5 " )
    //select avg(mark) from mark where stu_info_id=1 and

    @Select("select exemption.* from exemption WHERE   exemption.exemption_id=#{exemption_id} " )
    Exemption queryOneExemption(@Param("exemption_id") int exemption_id);


    /**
     * @param stu_info_id
     * @Description 计算学生最大免修id
     * @author 20155873 张联富
     * @methodName findMaxExemptionId
     * @returnType Exemption
     * @create 2018/5/13 20：24
     **/
    @Select("select * from exemption where exemption_id=( select max(exemption_id) from exemption where stu_info_id=#{stu_info_id})")
    Exemption findMaxExemptionId(@Param("stu_info_id") int stu_info_id);
}
